//-------------------------------------------------------------------
//文件名称：ECourse.cs
//模块名称：ECourse数据访问层
//功能说明：
//-----------------------------------------------------------------
//修改记录：
//修改人：Dawen
//-----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Models;
using ELearning.Models.ViewModel.YTJYAdmin.Course;

namespace ELearning.DAL
{
    /// <summary>
    /// ECourse数据访问层
    /// </summary>
	public partial class ECourseRepository : BaseRepository<ECourse>
    {
        /// <summary>
        /// 获取课程列表
        /// </summary>
	    public List<CourseModel> GetModelList(CourseRequest request)
        {
            var args = new List<SqlParameter>();
            var where = string.Empty;
            if (!string.IsNullOrEmpty(request.Name))
                where = $" AND v.Name LIKE N'%{request.Name}%'";

            if (!string.IsNullOrEmpty(request.CategoryId))
            {
                where = $" {where} AND v.CategoryId = @CategoryId";
                args.Add(new SqlParameter("@CategoryId", request.CategoryId));
            }

            if (!string.IsNullOrEmpty(request.TypeCode))
            {
                where = $" {where} AND v.TypeCode = @TypeCode";
                args.Add(new SqlParameter("@TypeCode", request.TypeCode));
            }

            var sql = $@"
                   SELECT
	                    b.*,
	                    cp.PaperId
                    FROM
	                    (
		                    SELECT
	                            *
                            FROM
	                            (
		                            SELECT
			                            v.*,
			                            d1.TypeName CategoryName,
			                            d2.TypeName ,
			                            ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                            COUNT(1) OVER() TotalCount
		                            FROM
			                            dbo.ECourse v
			                            LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                            LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
		                            WHERE
			                            v.IsDelete = 0  {where}
	                            )a
                            WHERE
	                            a.rn BETWEEN {request.StartIndex} AND {request.EndIndex}
	                    )b
	                    LEFT JOIN dbo.RCoursePaper cp ON cp.CourseId = b.Id AND  cp.IsDelete = 0";

            return nContext.Database.SqlQuery<CourseModel>(sql, args.ToArray()).ToList();
        }

        /// <summary>
        /// 获取课程视频关联或者不关联列表
        /// </summary>
        public List<CourseVideoModel> GetCourseVideoList(CourseVideoRequest request)
        {
            var where = "AND cv.CourseId IS NOT NULL";
            if (request.Type == 0)
                where = $" AND cv.CourseId IS NULL";
            if (!string.IsNullOrEmpty(request.Name))
                where = $" {where}  AND v.Name LIKE N'%{request.Name}%'";

            var sql = $@"
                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    cv.CourseId,
								v.Id,
								v.Name,
								v.Description,
			                    d1.TypeName CategoryName,
			                    d2.TypeName ,
			                    ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                    COUNT(1) OVER() TotalCount
		                    FROM
			                    dbo.EVideoLib v
								LEFT JOIN dbo.RCourseVideoLib cv ON cv.VideoLibId = v.Id AND cv.IsDelete = 0  AND cv.CourseId = '{request.CourseId}'
			                    LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                    LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
		                    WHERE
			                    v.IsDelete = 0  {where}
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";

            return nContext.Database.SqlQuery<CourseVideoModel>(sql).ToList();
        }

        /// <summary>
        /// 获取客户端课程列表
        /// </summary>
        public List<SiteCourseModel> GetSiteModelList(SiteCourseRequeset request, Guid userId)
        {
            var args = new List<SqlParameter>();
            var where = string.Empty;
            if (!string.IsNullOrEmpty(request.Name))
                where = $" AND v.Name LIKE N'%{request.Name}%'";

            if (request.PackageId != Guid.Empty)
            {
                where = $" {where} AND rpc.PackageId = @PackageId";
                args.Add(new SqlParameter("@PackageId", request.PackageId));
            }

            if (!string.IsNullOrEmpty(request.CategoryId))
            {
                where = $" {where} AND v.CategoryId = @CategoryId";
                args.Add(new SqlParameter("@CategoryId", request.CategoryId));
            }

            if (!string.IsNullOrEmpty(request.TypeCode))
            {
                where = $" {where} AND v.TypeCode = @TypeCode";
                args.Add(new SqlParameter("@TypeCode", request.TypeCode));
            }

            var sql = $@"
                        SELECT
			                v.*,
			                d1.TypeName CategoryName,
			                d2.TypeName ,
			                ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                COUNT(1) OVER() TotalCount,
							cv.VideoLibId,
							cv.ChapterId,
							c.PassGrade,c.Sort ChapterSort,
							c.Name ChapterName,
							vl.Name VideoLibName,
							vl.Status VideoLibStatus,
							vl.PlayUrl0 PlayUrl,
							vl.DownloadUrl,
							cp.PaperId,
							(SELECT TOP (1) h.TotalScore FROM dbo.TPaperTestHistory h WHERE h.PaperId = cp.PaperId AND h.ChapterId = c.Id AND h.UserId = @userId ORDER BY h.TotalScore DESC)Score,
							(SELECT COUNT(1) FROM dbo.EVideoStudySum vss WHERE vss.IsDelete = 0 AND vss.CourseId = cv.courseid AND vss.ChapterId = c.Id AND vss.UserId = @userId )StudyCount,
							(SELECT TOP (1) vss.ChapterId FROM dbo.EVideoStudySum vss WHERE vss.IsDelete = 0 AND vss.CourseId = cv.courseid AND vss.UserId = @userId ORDER BY vss.UpdateTime DESC)LastStudyChapterId,
							(SELECT TOP (1) vss.IsFinish FROM dbo.EVideoStudySum vss WHERE vss.IsDelete = 0 AND vss.CourseId = cv.courseid AND vss.UserId = @userId AND vss.ChapterId = c.Id ORDER BY vss.UpdateTime DESC)IsFinish,
							(SELECT TOP (1) vss.LastStudyTime FROM dbo.EVideoStudySum vss WHERE vss.IsDelete = 0 AND vss.CourseId = cv.courseid AND vss.UserId = @userId ORDER BY vss.UpdateTime DESC)LastStudyTime
		                FROM
							dbo.RPackageCourse rpc
			                LEFT JOIN dbo.ECourse v ON rpc.CourseId = v.Id
							LEFT JOIN dbo.TChapter c ON c.CourseId = v.Id AND c.IsDelete = 0
							LEFT JOIN dbo.RChapterVideoLib cv ON c.Id = cv.ChapterId AND cv.IsDelete = 0
							LEFT JOIN dbo.EVideoLib vl  ON vl.Id = cv.VideoLibId AND vl.IsDelete = 0
							LEFT JOIN dbo.RChapterPaper cp ON cp.ChapterId = c.Id AND cp.IsDelete = 0
			                LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
		                WHERE
			                v.IsDelete = 0 AND rpc.IsDelete = 0  {where}
	                    ORDER BY c.Sort,c.CreateTime";
            args.Add(new SqlParameter("@userId", userId));
            return nContext.Database.SqlQuery<SiteCourseModel>(sql, args.ToArray()).ToList();
        }

        /// <summary>
        /// 获取H5首页免费课程列表
        /// </summary>
        public List<FreeCourseModel> GetFreeModelList(FreeCourseRequest request)
        {
            var sql = $@"
                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    c.Id,
			                    c.Name,
			                    c.Description,
			                    c.CoverUrl,
			                    u.UserName,
			                    ROW_NUMBER() OVER(ORDER BY c.CreateTime DESC)rn,
			                    COUNT(1) OVER() TotalCount
		                    FROM
			                    dbo.ECourse c
			                    LEFT JOIN dbo.ESysUser u ON c.TeacherId = u.UserID AND u.IsDelete = 0
		                    WHERE
			                    c.IsDelete = 0 AND c.IsPublic = 1
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";

            return nContext.Database.SqlQuery<FreeCourseModel>(sql).ToList();
        }

        /// <summary>
        /// 获取免费课程详情
        /// </summary>
        public List<CourseDetailModel> GetModelDetailList(CourseDetailRequest request, Guid userId)
        {
            var sql = $@"
                        SELECT
							vl.Id,
							c.id ChapterId,c.PassGrade,c.Sort ChapterSort,
							c.Name,
							c.Description,
							vl.PlayUrl0 PlayUrl,
							vl.DownloadUrl,
							vl.Status,vl.Duration,cp.PaperId,
							(SELECT TOP (1) h.TotalScore FROM dbo.TPaperTestHistory h WHERE h.PaperId = cp.PaperId AND h.ChapterId = c.Id AND h.UserId = @userId ORDER BY h.TotalScore DESC)Score,
							(SELECT COUNT(1) FROM dbo.EVideoStudySum vss WHERE vss.IsDelete = 0 AND vss.CourseId = cv.courseid AND vss.ChapterId = c.Id AND vss.UserId = @userId )StudyCount,
							(SELECT TOP (1) vss.ChapterId FROM dbo.EVideoStudySum vss WHERE vss.IsDelete = 0 AND vss.CourseId = cv.courseid AND vss.UserId = @userId ORDER BY vss.UpdateTime DESC)LastStudyChapterId,
							(SELECT TOP (1) vss.IsFinish FROM dbo.EVideoStudySum vss WHERE vss.IsDelete = 0 AND vss.CourseId = cv.courseid AND vss.UserId = @userId AND vss.ChapterId = c.Id ORDER BY vss.UpdateTime DESC)IsFinish,
							(SELECT TOP (1) vss.LastStudyTime FROM dbo.EVideoStudySum vss WHERE vss.IsDelete = 0 AND vss.CourseId = cv.courseid AND vss.UserId = @userId ORDER BY vss.UpdateTime DESC)LastStudyTime
		                FROM
							dbo.RChapterVideoLib cv 
							LEFT JOIN dbo.TChapter c ON cv.ChapterId = c.Id
							LEFT JOIN dbo.EVideoLib vl  ON vl.Id = cv.VideoLibId AND vl.IsDelete = 0
							LEFT JOIN dbo.RChapterPaper cp ON cp.ChapterId = c.Id AND cp.IsDelete = 0
		                WHERE
			                cv.IsDelete = 0 AND c.IsDelete = 0
							AND cv.CourseId = @CourseId 
                        ORDER BY c.Sort,c.CreateTime ";
            var args = new List<SqlParameter>
            {
                new SqlParameter("@CourseId",request.CourseId),
                new SqlParameter("@userId",userId)
            };
            return nContext.Database.SqlQuery<CourseDetailModel>(sql, args.ToArray()).ToList();
        }
    }
}


